﻿<DataListSpec
	xmlns="bb_appfx_datalist"
	xmlns:common="bb_appfx_commontypes"
	ID="c96d978b-364a-4a40-bf91-c42a808297dd"
	Name="ConstituentsChangedSince Data List"
	Description="Get Constituent IDs for changed records."
	Author="Blackbaud Professional Services"
	common:SecurityUIFolder="Service Bus Adapter">

  <!-- define the SP that returns the data for the datalist -->
  <SPDataList SPName="USP_DATALIST_CONSTITUENTSCHANGEDSINCE">
    <common:CreateProcedureSQL>
      <![CDATA[
create procedure dbo.USP_DATALIST_CONSTITUENTSCHANGEDSINCE(
  @UPDATEDSINCE datetime, @CRMUSERNAME varchar(255))
as
	set nocount on;
  
  
  --SET @CRMUSERNAME = 'BLACKBAUDHOST\' + @CRMUSERNAME;
  -- User name should be passed in with Domain Prepended


--DECLARE @ChangeAgentGuid uniqueidentifier;
--SET @ChangeAgentGuid = (SELECT ID FROM CHANGEAGENT WHERE USERNAME = @CRMUSERNAME);  
  
SELECT DISTINCT c.ID 
  FROM CONSTITUENT c
  
    LEFT OUTER JOIN CHANGEAGENT  caConst  ON caConst.ID = c.CHANGEDBYID

    LEFT OUTER JOIN ADDRESS			a ON a.CONSTITUENTID = c.ID
      LEFT OUTER JOIN CHANGEAGENT  caAddr  ON caAddr.ID = a.CHANGEDBYID

    LEFT OUTER JOIN PHONE			p ON p.CONSTITUENTID = c.ID
      LEFT OUTER JOIN CHANGEAGENT  caPhone  ON caPhone.ID = p.CHANGEDBYID

    LEFT OUTER JOIN EMAILADDRESS	e ON e.CONSTITUENTID = c.ID
      LEFT OUTER JOIN CHANGEAGENT  caEmail  ON caEmail.ID = e.CHANGEDBYID
  
    LEFT OUTER JOIN REVENUE	r ON r.CONSTITUENTID = c.ID
      LEFT OUTER JOIN CHANGEAGENT  caRevenue  ON caRevenue.ID = r.CHANGEDBYID    

    LEFT OUTER JOIN PROSPECT	pr ON pr.ID = c.ID
      LEFT OUTER JOIN CHANGEAGENT  caProspect  ON caProspect.ID = pr.CHANGEDBYID    
    
    LEFT OUTER JOIN PROSPECTTEAM	prt ON prt.PROSPECTID = c.ID
      LEFT OUTER JOIN CHANGEAGENT  caProspectTeam  ON caProspectTeam.ID = prt.CHANGEDBYID    


WHERE	
/*
      (c.DATECHANGED >= @UPDATEDSINCE AND c.CHANGEDBYID <> @ChangeAgentGuid)
	OR	(a.DATECHANGED >= @UPDATEDSINCE AND a.CHANGEDBYID <> @ChangeAgentGuid)
	OR	(p.DATECHANGED >= @UPDATEDSINCE AND p.CHANGEDBYID <> @ChangeAgentGuid)
	OR	(e.DATECHANGED >= @UPDATEDSINCE AND e.CHANGEDBYID <> @ChangeAgentGuid)
  OR	((r.DATECHANGED >= @UPDATEDSINCE AND r.CHANGEDBYID <> @ChangeAgentGuid)
        AND r.TRANSACTIONTYPE = 'Pledge')
	OR	(pr.DATECHANGED >= @UPDATEDSINCE AND pr.CHANGEDBYID <> @ChangeAgentGuid)
  OR	(prt.DATECHANGED >= @UPDATEDSINCE AND prt.CHANGEDBYID <> @ChangeAgentGuid)
*/

      (c.DATECHANGED >= @UPDATEDSINCE AND caConst.USERNAME          <> @CRMUSERNAME)
	OR	(a.DATECHANGED >= @UPDATEDSINCE AND caAddr.USERNAME           <> @CRMUSERNAME)
	OR	(p.DATECHANGED >= @UPDATEDSINCE AND caPhone.USERNAME          <> @CRMUSERNAME)
	OR	(e.DATECHANGED >= @UPDATEDSINCE AND caEmail.USERNAME          <> @CRMUSERNAME)
  OR	((r.DATECHANGED >= @UPDATEDSINCE AND caRevenue.USERNAME       <> @CRMUSERNAME)
        AND r.TRANSACTIONTYPE = 'Pledge')
	OR	(pr.DATECHANGED >= @UPDATEDSINCE AND caProspect.USERNAME      <> @CRMUSERNAME)
  OR	(prt.DATECHANGED >= @UPDATEDSINCE AND caProspectTeam.USERNAME <> @CRMUSERNAME)

  
			]]>
    </common:CreateProcedureSQL>
  </SPDataList>


  <!-- describe any parameters (other than the context ID) defined on the SP -->
  <Parameters>
    <common:FormMetaData>
      <common:FormFields>
        <common:FormField FieldID="UPDATEDSINCE" Caption="Updated Since" DataType="Date"/>
        <common:FormField FieldID="CRMUSERNAME" Caption="CRM User Name" DataType="String"/>
      </common:FormFields>
    </common:FormMetaData>
  </Parameters>



  <!-- define the output fields in the list -->
  <Output>
    <OutputFields>
      <OutputField FieldID="ID" Caption="ID" DataType="Guid" IsHidden="true" />
    </OutputFields>
  </Output>

</DataListSpec>
